#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
挂机系统经验值问题修复脚本
基于诊断结果修复发现的问题
"""

import sys
import os
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from database.database_manager import DatabaseManager
from src.character.experience_system import ExperienceSystem
from src.idle.mysql_idle_manager import MySQLIdleManager
from datetime import datetime, timedelta
import time
import json

def print_section(title):
    print(f"\n{'='*50}")
    print(f" {title}")
    print(f"{'='*50}")

def fix_idle_exp_issue():
    """修复挂机系统经验值问题"""
    print("开始修复挂机系统经验值问题...")
    
    # 初始化数据库连接
    db_manager = DatabaseManager()
    exp_system = ExperienceSystem()
    idle_manager = MySQLIdleManager()
    
    try:
        # 1. 修复挂机记录的时长计算问题
        print_section("1. 修复挂机记录的时长计算问题")
        fix_duration_calculation(db_manager)
        
        # 2. 修复经验计算和更新机制
        print_section("2. 修复经验计算和更新机制")
        fix_exp_calculation_and_update(db_manager, exp_system)
        
        # 3. 创建自动经验更新机制
        print_section("3. 创建自动经验更新机制")
        create_auto_exp_update_mechanism(db_manager, exp_system)
        
        # 4. 验证修复效果
        print_section("4. 验证修复效果")
        verify_fix_results(db_manager, exp_system)
        
        print_section("修复完成")
        print("✅ 挂机系统经验值问题修复完成")
        
    except Exception as e:
        print(f"修复过程中发生错误: {e}")
        import traceback
        traceback.print_exc()

def fix_duration_calculation(db_manager):
    """修复挂机记录的时长计算问题"""
    print("修复挂机记录的时长计算问题...")
    
    # 查找duration_seconds为NULL或0但有开始和结束时间的记录
    query = """
    SELECT id, character_id, start_time, end_time, duration_seconds
    FROM idle_records
    WHERE (duration_seconds IS NULL OR duration_seconds = 0)
      AND start_time IS NOT NULL
      AND end_time IS NOT NULL
      AND status = 'completed'
    """
    
    records = db_manager.execute_query(query)
    
    if not records:
        print("没有找到需要修复的时长记录")
        return
    
    print(f"找到 {len(records)} 条需要修复的记录")
    
    fixed_count = 0
    for record in records:
        try:
            start_time = record['start_time']
            end_time = record['end_time']
            
            # 计算实际时长
            if isinstance(start_time, str):
                start_time = datetime.fromisoformat(start_time.replace('Z', '+00:00'))
            if isinstance(end_time, str):
                end_time = datetime.fromisoformat(end_time.replace('Z', '+00:00'))
            
            duration_seconds = int((end_time - start_time).total_seconds())
            
            if duration_seconds > 0:
                # 更新时长
                update_query = "UPDATE idle_records SET duration_seconds = %s WHERE id = %s"
                db_manager.execute_update(update_query, (duration_seconds, record['id']))
                
                print(f"  修复记录 {record['id']}: 时长 {duration_seconds}秒 ({duration_seconds/3600:.2f}小时)")
                fixed_count += 1
            
        except Exception as e:
            print(f"  ❌ 修复记录 {record['id']} 失败: {e}")
    
    print(f"✅ 成功修复 {fixed_count} 条记录的时长计算")

def fix_exp_calculation_and_update(db_manager, exp_system):
    """修复经验计算和更新机制"""
    print("修复经验计算和更新机制...")
    
    # 查找已完成但经验为0的挂机记录
    query = """
    SELECT ir.*, c.level, il.base_exp_rate
    FROM idle_records ir
    JOIN characters c ON ir.character_id = c.id
    JOIN idle_locations il ON ir.location_id = il.id
    WHERE ir.status = 'completed'
      AND ir.exp_gained = 0
      AND ir.duration_seconds > 0
    ORDER BY ir.created_at DESC
    LIMIT 20
    """
    
    records = db_manager.execute_query(query)
    
    if not records:
        print("没有找到需要修复经验的记录")
        return
    
    print(f"找到 {len(records)} 条需要重新计算经验的记录")
    
    fixed_count = 0
    total_exp_added = 0
    
    for record in records:
        try:
            # 计算应得经验
            duration_hours = record['duration_seconds'] / 3600
            base_rate = exp_system.calculate_idle_exp_rate(record['level'])
            location_multiplier = float(record['base_exp_rate'])
            
            calculated_exp = int(base_rate * location_multiplier * duration_hours)
            
            if calculated_exp > 0:
                # 更新挂机记录的经验
                update_record_query = "UPDATE idle_records SET exp_gained = %s WHERE id = %s"
                db_manager.execute_update(update_record_query, (calculated_exp, record['id']))
                
                # 更新角色经验
                update_char_query = "UPDATE characters SET exp = exp + %s WHERE id = %s"
                db_manager.execute_update(update_char_query, (calculated_exp, record['character_id']))
                
                print(f"  修复记录 {record['id']}: 添加 {calculated_exp} 经验 (时长: {duration_hours:.2f}h)")
                fixed_count += 1
                total_exp_added += calculated_exp
            
        except Exception as e:
            print(f"  ❌ 修复记录 {record['id']} 经验失败: {e}")
    
    print(f"✅ 成功修复 {fixed_count} 条记录，总计添加 {total_exp_added} 经验")

def create_auto_exp_update_mechanism(db_manager, exp_system):
    """创建自动经验更新机制"""
    print("创建自动经验更新机制...")
    
    # 创建存储过程来自动更新挂机经验
    procedure_sql = """
    DROP PROCEDURE IF EXISTS UpdateIdleExperience;
    
    CREATE PROCEDURE UpdateIdleExperience()
    BEGIN
        DECLARE done INT DEFAULT FALSE;
        DECLARE char_id VARCHAR(36);
        DECLARE loc_id INT;
        DECLARE char_level INT;
        DECLARE start_time TIMESTAMP;
        DECLARE base_rate DECIMAL(10,2);
        DECLARE duration_sec INT;
        DECLARE exp_to_add BIGINT;
        
        DECLARE cur CURSOR FOR
            SELECT ir.character_id, ir.location_id, c.level, ir.start_time, il.base_exp_rate,
                   TIMESTAMPDIFF(SECOND, ir.start_time, NOW()) as duration_seconds
            FROM idle_records ir
            JOIN characters c ON ir.character_id = c.id
            JOIN idle_locations il ON ir.location_id = il.id
            WHERE ir.status = 'active'
              AND TIMESTAMPDIFF(SECOND, ir.start_time, NOW()) >= 60; -- 至少挂机1分钟
        
        DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
        
        OPEN cur;
        
        read_loop: LOOP
            FETCH cur INTO char_id, loc_id, char_level, start_time, base_rate, duration_sec;
            IF done THEN
                LEAVE read_loop;
            END IF;
            
            -- 计算经验 (简化版本，实际应该调用经验系统)
            SET exp_to_add = FLOOR((char_level * 50 + 50) * base_rate * (duration_sec / 3600));
            
            IF exp_to_add > 0 THEN
                -- 更新角色经验
                UPDATE characters SET exp = exp + exp_to_add WHERE id = char_id;
                
                -- 更新挂机记录
                UPDATE idle_records 
                SET exp_gained = exp_gained + exp_to_add,
                    duration_seconds = duration_sec
                WHERE character_id = char_id AND status = 'active';
            END IF;
            
        END LOOP;
        
        CLOSE cur;
    END;
    """
    
    try:
        # 执行存储过程创建
        db_manager.execute_update(procedure_sql)
        print("✅ 成功创建自动经验更新存储过程")
        
        # 创建定时更新触发器（每分钟检查一次）
        event_sql = """
        DROP EVENT IF EXISTS auto_update_idle_exp;
        
        CREATE EVENT auto_update_idle_exp
        ON SCHEDULE EVERY 1 MINUTE
        DO
            CALL UpdateIdleExperience();
        """
        
        db_manager.execute_update(event_sql)
        print("✅ 成功创建自动经验更新定时事件")
        
        # 启用事件调度器
        db_manager.execute_update("SET GLOBAL event_scheduler = ON;")
        print("✅ 启用事件调度器")
        
    except Exception as e:
        print(f"❌ 创建自动更新机制失败: {e}")
        # 如果存储过程创建失败，创建一个Python版本的更新函数
        create_python_auto_update(db_manager, exp_system)

def create_python_auto_update(db_manager, exp_system):
    """创建Python版本的自动更新函数"""
    print("创建Python版本的自动更新函数...")
    
    update_script = '''
#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
挂机经验自动更新脚本
定期运行以更新正在挂机角色的经验
"""

import sys
import os
sys.path.append(os.path.dirname(os.path.abspath(__file__)))

from database.database_manager import DatabaseManager
from src.character.experience_system import ExperienceSystem
from datetime import datetime

def update_idle_experience():
    """更新挂机经验"""
    db_manager = DatabaseManager()
    exp_system = ExperienceSystem()
    
    try:
        # 查找正在挂机的角色
        query = """
        SELECT ir.id, ir.character_id, ir.location_id, c.level, ir.start_time, 
               il.base_exp_rate, ir.exp_gained,
               TIMESTAMPDIFF(SECOND, ir.start_time, NOW()) as current_duration
        FROM idle_records ir
        JOIN characters c ON ir.character_id = c.id
        JOIN idle_locations il ON ir.location_id = il.id
        WHERE ir.status = 'active'
          AND TIMESTAMPDIFF(SECOND, ir.start_time, NOW()) >= 60
        """
        
        records = db_manager.execute_query(query)
        
        if not records:
            print("没有需要更新经验的挂机角色")
            return
        
        print(f"更新 {len(records)} 个挂机角色的经验...")
        
        for record in records:
            try:
                # 计算新增经验
                duration_hours = record['current_duration'] / 3600
                base_rate = exp_system.calculate_idle_exp_rate(record['level'])
                location_multiplier = float(record['base_exp_rate'])
                
                total_exp = int(base_rate * location_multiplier * duration_hours)
                new_exp = total_exp - (record['exp_gained'] or 0)
                
                if new_exp > 0:
                    # 更新角色经验
                    update_char_query = "UPDATE characters SET exp = exp + %s WHERE id = %s"
                    db_manager.execute_update(update_char_query, (new_exp, record['character_id']))
                    
                    # 更新挂机记录
                    update_record_query = """
                    UPDATE idle_records 
                    SET exp_gained = %s, duration_seconds = %s 
                    WHERE id = %s
                    """
                    db_manager.execute_update(update_record_query, 
                                            (total_exp, record['current_duration'], record['id']))
                    
                    print(f"  角色 {record['character_id']}: +{new_exp} 经验 (总计: {total_exp})")
                
            except Exception as e:
                print(f"  更新角色 {record['character_id']} 经验失败: {e}")
        
        print("经验更新完成")
        
    except Exception as e:
        print(f"更新挂机经验失败: {e}")

if __name__ == "__main__":
    update_idle_experience()
'''
    
    # 写入自动更新脚本
    with open('auto_update_idle_exp.py', 'w', encoding='utf-8') as f:
        f.write(update_script)
    
    print("✅ 创建Python自动更新脚本: auto_update_idle_exp.py")
    print("   建议设置定时任务每分钟运行一次该脚本")

def verify_fix_results(db_manager, exp_system):
    """验证修复效果"""
    print("验证修复效果...")
    
    # 1. 检查时长修复效果
    print("\n1. 检查时长修复效果:")
    query = """
    SELECT COUNT(*) as count
    FROM idle_records
    WHERE (duration_seconds IS NULL OR duration_seconds = 0)
      AND start_time IS NOT NULL
      AND end_time IS NOT NULL
      AND status = 'completed'
    """
    result = db_manager.execute_query(query)
    remaining_duration_issues = result[0]['count'] if result else 0
    
    if remaining_duration_issues == 0:
        print("  ✅ 所有已完成记录的时长都已正确计算")
    else:
        print(f"  ⚠️  仍有 {remaining_duration_issues} 条记录的时长需要修复")
    
    # 2. 检查经验修复效果
    print("\n2. 检查经验修复效果:")
    query = """
    SELECT COUNT(*) as count
    FROM idle_records
    WHERE status = 'completed'
      AND exp_gained = 0
      AND duration_seconds > 0
    """
    result = db_manager.execute_query(query)
    remaining_exp_issues = result[0]['count'] if result else 0
    
    if remaining_exp_issues == 0:
        print("  ✅ 所有有时长的记录都已正确计算经验")
    else:
        print(f"  ⚠️  仍有 {remaining_exp_issues} 条记录的经验为0")
    
    # 3. 检查当前挂机状态
    print("\n3. 检查当前挂机状态:")
    query = """
    SELECT c.name, c.level, c.exp, ir.start_time,
           TIMESTAMPDIFF(SECOND, ir.start_time, NOW()) as current_duration,
           ir.exp_gained
    FROM characters c
    JOIN idle_records ir ON c.id = ir.character_id
    WHERE ir.status = 'active'
    """
    
    active_records = db_manager.execute_query(query)
    
    if active_records:
        print(f"  当前有 {len(active_records)} 个角色正在挂机:")
        for record in active_records:
            duration_hours = (record['current_duration'] or 0) / 3600
            print(f"    {record['name']} (Lv.{record['level']}): 已挂机 {duration_hours:.2f}h, 获得 {record['exp_gained'] or 0} 经验")
    else:
        print("  当前没有角色在挂机")
    
    # 4. 测试经验计算
    print("\n4. 测试经验计算:")
    test_levels = [1, 5, 10]
    for level in test_levels:
        base_rate = exp_system.calculate_idle_exp_rate(level)
        print(f"  等级 {level}: 基础经验速率 = {base_rate:.1f}/小时")
    
    print("\n✅ 修复效果验证完成")

if __name__ == "__main__":
    fix_idle_exp_issue()